from django.utils import timezone
from django.db.models import Q, Count, QuerySet, Max
from django.db import models

from django.db.models import Aggregate, CharField


class Concat(Aggregate):
    """ORM用来分组显示其他字段 相当于group_concat"""
    function = 'GROUP_CONCAT'
    template = '%(function)s(%(distinct)s%(expressions)s)'

    def __init__(self, expression, distinct=False, **extra):
        super(Concat, self).__init__(
            expression,
            distinct='DISTINCT ' if distinct else '',
            output_field=CharField(),
            **extra)


# Create your models here.
class AdminManager(models.Manager):
    def isExist(self, admin_account, admin_password):
        admin = self.filter(admin_account=admin_account, admin_password=admin_password)
        if admin.exists():
            return admin.first()
        else:
            return None


class Admin(models.Model):
    class Meta:
        db_table = 'chat_admin'

    admin_account = models.CharField(max_length=16)
    admin_password = models.CharField(max_length=16)
    objects = AdminManager()


class UserManager(models.Manager):
    def add(self, user_account, user_password, nickname, head_img, sex, mobile_number):
        user = self.create(user_account=user_account, user_password=user_password, nickname=nickname, head_img=head_img,
                           sex=sex, mobile_number=mobile_number)
        # do something with the book
        return user

    def getList(self, page, number):
        offset = (int(page) - 1) * int(number)
        return self.all()[offset:offset + int(number)]

    def isRegistered(self, user_account, mobile_number):
        user = self.filter(Q(is_del=0) & (Q(user_account=user_account) | Q(mobile_number=mobile_number))).count()
        return user

    def update(self, id, user_account, user_password, nickname, head_img, sex, mobile_number):
        user = self.get(id)
        user.user_account = user_account
        user.user_password = user_password
        user.nickname = nickname
        user.head_img = head_img
        user.sex = sex
        user.mobile_number = mobile_number
        user.save()
        return user

    def searchUser(self, keyword):
        user = self.filter(Q(user_account__contains=keyword) | Q(nickname__contains=keyword)).all()
        return user

    def getUserCount(self):
        user = self.count()
        return user

    def delUser(self, user_id):
        user = self.get(user_id)
        user.isdel = 1
        user.save()
        return user

    def restoreUser(self, user_id):
        user = self.get(user_id)
        user.is_del = 0
        user.save()
        return user

    def isExist(self, user_account, user_password):
        user = self.filter(user_account=user_account, user_password=user_password)
        if user.exists():
            return user.first()
        else:
            return None


class User(models.Model):
    class Meta:
        db_table = 'chat_user'

    user_account = models.CharField(max_length=16)
    user_password = models.CharField(max_length=16)
    nickname = models.CharField(max_length=16)
    head_img = models.CharField(max_length=255)
    sex = models.CharField(max_length=1)
    mobile_number = models.CharField(max_length=13)
    is_del = models.IntegerField(default=0)
    create_time = models.DateTimeField(default=timezone.now)

    objects = UserManager()


class MessageManager(models.Manager):
    def getLastMessageList(self, user_id):
        query = self.filter(Q(send_user_id=user_id) & ~Q(receive_user_id=user_id)).values('receive_user_id').annotate(
            id=Max('id'), content_=Concat('content')).all().query
        message = QuerySet(query=query, model=Message)
        return message

    def getMessageList(self, user_id, friend_id):
        query = self.filter(is_del=0).filter((Q(receive_user_id=user_id) & Q(send_user_id=friend_id)) | (
                    Q(send_user_id=user_id) & Q(receive_user_id=friend_id))).all().query
        message = QuerySet(query=query, model=Message)
        return message

    def saveMessage(self, user_id, friend_id, content):
        msg = self.create(send_user_id=user_id, receive_user_id=friend_id, content=content)
        return msg

    def getMessageCountByUser(self, id, time_start, time_end):
        message = self.extra(select={'date': 'date(create_time)'}).filter(send_user_id=id,
                                                                          create_time__range=[time_start,
                                                                                              time_end]).values(
            'date').annotate(count=Count('create_time')).all()
        return message


class Message(models.Model):
    class Meta:
        db_table = 'chat_message'
        ordering = []

    send_user = models.ForeignKey(User, on_delete=models.CASCADE)
    receive_user_id = models.IntegerField(name='receive_user_id')
    content = models.TextField()
    create_time = models.DateTimeField(default=timezone.now)
    is_del = models.IntegerField(default=0)
    objects = MessageManager()
